package com.situ.library.dao.impl;

import com.situ.library.dao.IRecordDao;
import com.situ.library.pojo.entity.Record;
import com.situ.library.pojo.query.RecordQuery;
import com.situ.library.pojo.vo.RecordVO;
import com.situ.library.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class RecordDaoImpl implements IRecordDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());



    @Override
    public List<RecordVO> selectByPage(RecordQuery recordQuery) {

        String updateSql = "UPDATE record AS r " +
                "SET r.status_id = CASE " +
                "    WHEN r.status_id = 3 THEN '3' " +
                "    WHEN r.status_id = 4 THEN '4' " +
                "    WHEN DATE_ADD(DATE_ADD(r.create_time, INTERVAL r.day DAY), INTERVAL r.day2 DAY) > NOW() THEN '1' " +
                "    ELSE '2' " +
                "END";

        // 执行更新操作
        template.update(updateSql);

        String sql = "SELECT r.id, r.create_time,\n" +
                "DATE_ADD(DATE_ADD(r.create_time, INTERVAL r.day DAY), INTERVAL r.day2 DAY) AS update_time, \n" +
                "r.day, r.day2, \n" +
                "s.id AS statusId, s.`name` AS statusName,\n" +
                "b.id AS bookId, b.`name` AS bookName, " +
                "c.id AS cardId, c.`name` AS cardName\n" +
                "FROM record AS r \n" +
                "INNER JOIN book AS b ON r.book_id = b.id \n" +
                "INNER JOIN status AS s ON r.status_id = s.id \n" +
                "INNER JOIN card AS c ON r.card_id = c.id where 1=1";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        if (!ObjectUtils.isEmpty(recordQuery.getBookName())) {
            sql += " and b.`name`=?";
            queryList.add(recordQuery.getBookName());
        }
        if (!ObjectUtils.isEmpty(recordQuery.getCardName())) {
            sql += " and c.`name`=?";
            queryList.add(recordQuery.getCardName());
        }
        if (!ObjectUtils.isEmpty(recordQuery.getBeginCreateTime()) &&
                !ObjectUtils.isEmpty(recordQuery.getEndCreateTime())) {
            sql += " and r.create_time between ? and ?";
            queryList.add(recordQuery.getBeginCreateTime());
            queryList.add(recordQuery.getEndCreateTime());
        }
        sql += " limit ?,?";
        int offset = (recordQuery.getPage() - 1) * recordQuery.getLimit();
        queryList.add(offset);
        queryList.add(recordQuery.getLimit());
        List<RecordVO> list = template.query(sql,
                new BeanPropertyRowMapper<>(RecordVO.class), queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(RecordQuery recordQuery) {
        String sql = "SELECT count(*)\n" +
                "FROM record AS r " +
                "INNER JOIN book AS b ON r.book_id = b.id " +
                "INNER JOIN card AS c ON r.card_id = c.id where 1=1";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        String queryName = recordQuery.getName();
        if (queryName != null && !queryName.equals("")) {
            sql += " and b.'name' like ?";
            queryList.add("%" + queryName + "%");
        }
        if (!ObjectUtils.isEmpty(recordQuery.getCardName())) {
            sql += " and c.`name`=?";
            queryList.add(recordQuery.getCardName());
        }
        if (!ObjectUtils.isEmpty(recordQuery.getBeginCreateTime()) &&
                !ObjectUtils.isEmpty(recordQuery.getEndCreateTime())) {
            sql += " and r.create_time between ? and ?";
            queryList.add(recordQuery.getBeginCreateTime());
            queryList.add(recordQuery.getEndCreateTime());
        }
        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }


    @Override
    public void add(Record record) {
        template.update("insert into record(book_id,card_id,day) values(?,?,?)",
                record.getBookId(), record.getCardId(),record.getDay());
    }

    @Override
    public Record selectById(int id) {
        return template.queryForObject("select * from record where id=?",
                new BeanPropertyRowMapper<>(Record.class), id);
    }

    @Override
    public void update(Record record) {
        template.update("update record set day2=?,book_id=?,card_id=?,status_id=? where id=?",
                record.getDay2(),record.getBookId(), record.getCardId(), record.getStatusId(),record.getId());
    }

    @Override
    public void updateStatus(int id, int status) {
        template.update("update record set status_id=? where id=?",
                status, id);
    }

}